﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using VehicleConfigurator.Database;
using System.Data;
using VehicleConfigurator.Model;
using log4net;
using System.Windows.Forms;
using System.IO;
using VehicleConfigurator.NetUtils.Model;
using VehicleConfigurator.NetUtils.Settings;
using CsvHelper;

namespace VehicleConfigurator.NetUtils.Database
{
    public static class DBAccess
    {

        static readonly ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); //Constructing log
        public static DataTable GetMakes()
        {
            var db = new clsDb();
            var query = "SELECT * FROM make ORDER BY Name ";
            var dt = new DataTable();
            return db.GetDataTable(query);
        }

        public static DataTable GetModels(string makeOid)
        {
            var db = new clsDb();
            var cond = string.IsNullOrEmpty(makeOid) ? string.Empty : string.Format("WHERE MakeOID = {0}", makeOid);
            var query = string.Format("SELECT * FROM model {0} ORDER BY Name ", cond);
            var dt = new DataTable();
            return db.GetDataTable(query);
        }

        public static DataTable GetModels()
        {
            return GetModels(string.Empty);
        }

        public static DataTable GetSubModel(string modelOid, string modelEditionOid, string modelYearOid)
        {
            var db = new clsDb();
            var cond = "WHERE 1 = 1";
            cond += string.IsNullOrEmpty(modelOid) ? string.Empty : " " + string.Format("AND sm.ModelOID = {0}", modelOid);
            cond += string.IsNullOrEmpty(modelEditionOid) ? string.Empty : " " + string.Format("AND sm.ModelEditionOID = {0}", modelEditionOid);
            cond += string.IsNullOrEmpty(modelYearOid) ? string.Empty : " " + string.Format("AND sm.ModelYearOID = {0}", modelYearOid);
            var query = string.Format("SELECT * FROM submodel sm {0} ORDER BY sm.Name ", cond);
            var dt = new DataTable();
            return db.GetDataTable(query);
        }

        public static DataTable GetSubModel()
        {
            return GetSubModel(string.Empty, string.Empty, string.Empty);
        }

        public static DataTable GetModelEdition(string modelOid)
        {
            var db = new clsDb();
            var cond = string.IsNullOrEmpty(modelOid) ? string.Empty : string.Format("WHERE ModelOID = {0}", modelOid);
            var query = string.Format("SELECT * FROM modeledition {0} ORDER BY Name ", cond);
            var dt = new DataTable();
            if (string.IsNullOrEmpty(modelOid))
            {
                return dt;
            }
            else
            {
                return db.GetDataTable(query);
            }
        }

        public static ModelEdition GetModelEditionBy(string makeName, string modelName, string modelEditionName)
        {
            var sb = new StringBuilder();
            sb.Append("SELECT me.OID as OID, me.`Name` as ModelEdition, me.ModelOID, mo.`Name` as Model, mo.MakeOID, m.`Name` as Make ")
            .Append("FROM `modeledition` me, `model` mo, `make` m ")
            .Append("WHERE me.ModelOID = mo.OID AND mo.MakeOID = m.OID ");
            var cond = false;
            if (!string.IsNullOrEmpty(makeName))
            {
                sb.Append(string.Format("AND m.Name = '{0}' ", makeName));
                cond = true;
            }
            if (!string.IsNullOrEmpty(modelName))
            {
                sb.Append(string.Format("AND mo.Name = '{0}' ", modelName));
                cond = true;
            }
            if (!string.IsNullOrEmpty(modelEditionName))
            {
                sb.Append(string.Format("AND me.Name = '{0}' ", modelEditionName));
                cond = true;
            }
            var query = sb.ToString();
            if (!cond)
            {
                log.Debug(", getModelEditionBy: " + query);
                return null;
            }
            log.Debug(", getModelEditionBy: " + query);
            var db = new clsDb();
            var dt = db.GetDataTable(query);
            if (dt.Rows.Count > 0)
            {
                var dataRow = dt.Rows[0];
                var edition = new ModelEdition();
                var model = new VehicleConfigurator.Model.Model();
                var make = new Make();
                make.OID = (int)dataRow[FieldNames.OID_MAKE];
                make.Name = makeName;
                model.OID = (int)dataRow[FieldNames.OID_MODEL];
                model.Name = modelName;
                model.Make = make;
                edition.ModelOID = model.OID;
                edition.Name = modelEditionName;
                edition.OID = (int)dataRow[FieldNames.OID];
                edition.Model = model;
                return edition;
            }
            else
            {
                return null;
            }
        }

        public static int GetModelEditionOidByName(int modelOid, string modelEditionName)
        {
            var db = new clsDb();
            var cond = string.Format("WHERE ModelOID = {0} AND name like '{1}'", modelOid, modelEditionName);
            var query = string.Format("SELECT * FROM modeledition {0} ", cond);
            var result = db.GetDataTable(query);
            if (result != null && result.Rows.Count > 0)
            {
                return (int)result.Rows[0][FieldNames.OID];
            }
            else
            {
                return -1;
            }
        }

        public static DataTable GetModelEdition()
        {
            return GetModelEdition(string.Empty);
        }

        public static DataTable GetModelYear(string makeOid)
        {
            var db = new clsDb();
            var cond = string.IsNullOrEmpty(makeOid) ? string.Empty : string.Format("WHERE MakeOID = {0}", makeOid);
            var query = string.Format("SELECT * FROM modelyear {0} ORDER BY Name ", cond);
            var dt = new DataTable();
            return db.GetDataTable(query);
        }

        public static DataTable GetModelYear()
        {
            return GetModelYear(string.Empty);
        }

        public static Option GetOption(string optionCode, int groupOid)
        {
            var db = new clsDb();
            var query = "SELECT * FROM `option` o WHERE 1 = 1";
            // optionCode
            query += string.Format(" AND o.optioncode = '{0}'", optionCode);
            // groupOid
            query += string.Format(" AND o.groupoid = {0}", groupOid);
            var dt = db.GetDataTable(query);
            if (dt.Rows.Count > 0)
            {
                var option = new Option();
                var dataRow = dt.Rows[0];
                option.OID = (int)dataRow[FieldNames.OID];
                option.GroupOID = (int)dataRow[FieldNames.OID_GROUP];
                option.OptionCode = dataRow[FieldNames.OPTION_CODE] as string;
                option.BaseName = dataRow[FieldNames.BASE_NAME] as string;
                option.Explanation = dataRow[FieldNames.EXPLANATION] as string;
                return option;
            }
            return null;
        }

        public static OptionGroup GetOptionGroup(int makeOid, string groupName)
        {
            var db = new clsDb();
            var query = "SELECT * FROM `optiongroup` g WHERE 1 = 1";
            // optionCode
            query += string.Format(" AND g.MakeOid = {0}", makeOid);
            // groupName
            query += string.Format(" AND g.Name = '{0}'", groupName);
            var dt = db.GetDataTable(query);
            if (dt.Rows.Count > 0)
            {
                var group = new OptionGroup();
                var dataRow = dt.Rows[0];
                group.OID = (int)dataRow[FieldNames.OID];
                group.MakeOID = (int)dataRow[FieldNames.OID_MAKE];
                group.Name = dataRow[FieldNames.NAME] as string;
                return group;
            }
            return null;
        }

        public static OptionName GetOptionName(int optionOid, int langOid)
        {
            var db = new clsDb();
            var query = "SELECT * FROM `optionname` g WHERE 1 = 1";
            // optionCode
            query += string.Format(" AND g.OptionOID = {0}", optionOid);
            // groupOid
            query += string.Format(" AND g.LanguageOID = '{0}'", langOid);
            var dt = db.GetDataTable(query);
            if (dt.Rows.Count > 0)
            {
                var optionName = new OptionName();
                var dataRow = dt.Rows[0];
                optionName.OID = (int)dataRow[FieldNames.OID];
                optionName.OptionOID = (int)dataRow[FieldNames.OID_OPTION];
                optionName.LanguageOID = (int)dataRow[FieldNames.OID_LANGUAGE];
                optionName.Name = dataRow[FieldNames.NAME] as string;
                return optionName;
            }
            return null;
        }


        public static DataTable GetOptionGroup(string makeOid)
        {
            var db = new clsDb();
            var cond = string.IsNullOrEmpty(makeOid) ? string.Empty : string.Format("WHERE MakeOID = {0}", makeOid);
            var query = string.Format("SELECT * FROM optiongroup {0} ORDER BY Name ", cond);
            var dt = new DataTable();
            dt = db.GetDataTable(query);
            return dt;
        }

        public static DataTable GetOptionGroup()
        {
            return GetOptionGroup(string.Empty);
        }

        public static DataTable GetCurrency(string code)
        {
            var db = new clsDb();
            var cond = string.IsNullOrEmpty(code) ? string.Empty : string.Format("WHERE Code = '{0}'", code);
            var query = string.Format("SELECT * FROM currency {0} ORDER BY Code ", cond);
            var dt = new DataTable();
            return db.GetDataTable(query);
        }

        public static DataTable GetCurrency()
        {
            return GetCurrency(string.Empty);
        }

        public static DataTable GetCountry(string code)
        {
            var db = new clsDb();
            var cond = string.IsNullOrEmpty(code) ? string.Empty : string.Format("WHERE Code = '{0}'", code);
            var query = string.Format("SELECT * FROM country {0} ORDER BY Code ", cond);
            var dt = new DataTable();
            return db.GetDataTable(query);
        }

        public static DataTable GetCountry()
        {
            return GetCountry(string.Empty);
        }

        public static DataTable GetLanguage(string code)
        {
            var db = new clsDb();
            var cond = string.IsNullOrEmpty(code) ? string.Empty : string.Format("WHERE Code = '{0}'", code);
            var query = string.Format("SELECT * FROM language {0} ORDER BY Code ", cond);
            var dt = new DataTable();
            return db.GetDataTable(query);
        }

        public static DataTable GetLanguage()
        {
            return GetLanguage(string.Empty);
        }

        //public static DataTable GetOptionPricelist(string code)
        //{
        //    var db = new clsDb();
        //    var cond = string.IsNullOrEmpty(code) ? string.Empty : string.Format("AND PriceListDate = '{0}'", code);
        //    var query = string.Format("SELECT op.OID as OID, og.Name as GROUPNAME, o.OptionCode as OptionCode, o.Explanation as Explanation, o.BaseName as BaseName, me.Name as Edition, 1 as Active, 1 as Cosmo, 1 as OPC, `TaxPercentage1`, `TaxPercentage2`, `TaxPercentage3`, `TaxAmount1`, `TaxAmount2`, `TaxAmount3`, `DealerNetPrice`, `RetailPrice`, `DealerNetPriceVAT`, `RetailPriceVAT`, PriceListDate "
        //        + "FROM `optionprice` op, `option` o, `optiongroup` og, `modeledition` me "
        //        + "WHERE op.OptionOID = o.OID AND o.GroupOID = og.OID AND op.ModelEditionOID  = me.OID {0} ORDER BY PriceListDate DESC", cond);
        //    var dt = new DataTable();
        //    return db.GetDataTable(query);
        //}

        //public static DataTable GetOptionPricelist()
        //{
        //    return GetOptionPricelist(string.Empty);
        //}

        public static DataTable GetModelPricelist(string makeOid, string modelOid, string modelYearOid,
            string countryOid, string currencyOid, string pricelistDate)
        {
            var db = new clsDb();
            var cond = "WHERE 1 = 1";
            cond += " AND smp.SubmodelOID = sm.OID";
            // Make
            //cond += string.IsNullOrEmpty(makeOid) ? string.Empty : " " + string.Format("AND m.MakeOID = {0}", makeOid);
            // Model
            //cond += string.IsNullOrEmpty(modelOid) ? string.Empty : " " + string.Format("AND sm.ModelOID = {0}", modelOid);
            cond += " " + string.Format("AND sm.ModelOID = '{0}'", string.IsNullOrEmpty(modelOid) ? string.Empty : modelOid);
            // Model Year
            cond += " " + string.Format("AND sm.ModelYearOID = '{0}'", string.IsNullOrEmpty(modelYearOid) ? string.Empty : modelYearOid);
            // Country
            cond += " " + string.Format("AND smp.CountryOID = '{0}'", string.IsNullOrEmpty(countryOid) ? string.Empty : countryOid);
            // Currency
            cond += " " + string.Format("AND smp.CurrencyOID = '{0}'", string.IsNullOrEmpty(currencyOid) ? string.Empty : currencyOid);
            // Pricelist date
            cond += " " + string.Format("AND smp.PriceListDate = STR_TO_DATE('{0}', '%Y.%m.%d')", string.IsNullOrEmpty(pricelistDate) ? string.Empty : pricelistDate);
            var query = string.Format("SELECT smp.OID as OID, sm.OID as SubModelOID, sm.ModelOID, sm.ModelYearOID, smp.CountryOID, smp.CurrencyOID,  sm.ModelEditionOID, sm.ModelCode as ModelCode, sm.Name as NAME, sm.CylinderCapacity, sm.EngineType, sm.PowerKW, sm.GearBoxType, sm.CO2EmissionMin, sm.CO2EmissionMax, sm.CO2EmissionAvg, sm.EmissionNorm, sm.FuelType,  `TaxPercentage1`, `TaxPercentage2`, `TaxPercentage3`, `TaxAmount1`, `TaxAmount2`, `TaxAmount3`, `DealerNetPrice`, `RetailPrice`, `DealerNetPriceVAT`, `RetailPriceVAT`, `PriceListDate`  "
                + "FROM `submodelprice` smp, `submodel` sm "
                + "{0} ORDER BY PriceListDate DESC", cond);
            var dt = new DataTable();
            return db.GetDataTable(query);
        }

        public static DataTable GetModelPricelistExport(string makeOid, string modelOid, string modelYearOid,
            string countryOid, string currencyOid, string pricelistDate)
        {
            var db = new clsDb();
            var cond = "WHERE 1 = 1";
            cond += "  ";
            // Make
            //cond += string.IsNullOrEmpty(makeOid) ? string.Empty : " " + string.Format("AND m.MakeOID = {0}", makeOid);
            // Model
            cond += string.IsNullOrEmpty(modelOid) ? string.Empty : " " + string.Format("AND submodel.ModelOID in ({0})", modelOid);
            // Model Year
            cond += string.IsNullOrEmpty(modelYearOid) ? string.Empty : " " + string.Format("AND submodel.ModelYearOID = {0}", modelYearOid);
            // Country
            cond += string.IsNullOrEmpty(countryOid) ? string.Empty : " " + string.Format("AND submodelprice.CountryOID = {0}", countryOid);
            // Currency
            cond += string.IsNullOrEmpty(currencyOid) ? string.Empty : " " + string.Format("AND submodelprice.CurrencyOID = {0}", currencyOid);
            // Pricelist date
            cond += string.IsNullOrEmpty(pricelistDate) ? string.Empty : " " + string.Format("AND submodelprice.PriceListDate = STR_TO_DATE('{0}', '%Y.%m.%d')", pricelistDate);
            //var query = string.Format("SELECT ma.`Name` as Make , my.`Name` as ModelYearName,sm.ModelCode AS ModelCode,	me.`Name` as EditionCode,	m. NAME as ModelName,	sm. NAME AS SubmodelName, "
            //+ " `DealerNetPrice`, `RetailPrice`, `DealerNetPriceVAT`, `RetailPriceVAT`,smp.TaxPercentage1,smp.TaxPercentage2,smp.TaxPercentage3,smp.TaxAmount1,smp.TaxAmount2,smp.TaxAmount3,sm.CylinderCapacity,sm.EngineType,sm.PowerKW,sm.GearBoxType,sm.CO2EmissionMin,sm.CO2EmissionMax,sm.CO2EmissionAvg,sm.EmissionNorm,sm.FuelType "
            //    + "FROM `submodelprice` smp, `submodel` sm,model m,modelyear my, modeledition me , make ma "
            //    + "{0} ORDER BY PriceListDate DESC", cond);
            var query = string.Format("SELECT make.`Name` AS Make,modelyear.`Name` AS ModelYearName,submodel.ModelCode AS ModelCode,submodel.ModelEditionOID AS EditionCode,model.`Name` AS ModelName,submodel.`Name` AS SubmodelName,"
           + " submodelprice.PriceListDate,submodelprice.TaxPercentage1,submodelprice.TaxPercentage2,submodelprice.TaxPercentage3,submodelprice.TaxAmount1,submodelprice.TaxAmount2,submodelprice.TaxAmount3,submodelprice.DealerNetPrice,submodelprice.RetailPrice,submodelprice.DealerNetPriceVAT,submodelprice.RetailPriceVAT,"
           + " submodel.CylinderCapacity,submodel.EngineType,submodel.PowerKW,submodel.GearBoxType,submodel.CO2EmissionMin,submodel.CO2EmissionMax,submodel.CO2EmissionAvg,submodel.EmissionNorm,submodel.FuelType "
           + " FROM make INNER JOIN model ON model.MakeOID = make.OID "
           + " INNER JOIN modeledition ON modeledition.ModelOID = model.OID "
           + " INNER JOIN modelyear ON modelyear.MakeOID = make.OID "
           + " INNER JOIN submodel ON submodel.ModelYearOID = modelyear.OID AND submodel.ModelOID = model.OID AND modeledition.OID = submodel.ModelEditionOID "
           + " INNER JOIN submodelprice ON submodelprice.SubmodelOID = submodel.OID "
               + "{0} ORDER BY PriceListDate DESC", cond);

            var dt = new DataTable();
            return db.GetDataTable(query);
        }

        public static DataTable GetModelPricelist()
        {
            return GetModelPricelist(string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty);
        }

        public static DataTable GetModelPricelistEmpty()
        {
            var query = "select op.*, op.OID as OptionOid, opc.PriceListDate as PriceListDate, opc.DealerNetPrice,opc.DealerNetPriceVAT,opc.RetailPrice,opc.RetailPriceVAT, "
                        + "opc.TaxAmount1,opc.TaxAmount2,opc.TaxAmount3, opc.TaxPercentage1,opc.TaxPercentage2,opc.TaxPercentage3,opc.OID as optionpricelistOid, me.OID as modeleditionOID,opc.CountryOID,opc.ModelYearOID,opc.CurrencyOID "
                        + "from `option` op, optiongroup opg , modeledition  me, optionprice opc , modelyear my, country cou, currency cur, model mo "
                        + "where 1 = 2";
            var db = new clsDb();
            return db.GetDataTable(query);
        }




        public static DataTable GetOptionPricelist(string modelOid, string groupOid, string modelYearOid,
           string countryOid, string currencyOid, string pricelistDate, string makeOid)
        {
            var sb = new StringBuilder();
            sb.Append("select GROUP_CONCAT(CONCAT(opc.OID, '|', opc.ModelEditionOID)) as OptionPricelistOid")
                .Append(",GROUP_CONCAT(opc.OID) as OptionPricelistOids")
                .Append(",GROUP_CONCAT(DISTINCT opc.ModelEditionOID) as ModelEditionOID")
                .Append(",op.BaseName,op.Explanation,op.OptionCode,op.GroupOID,opc.OptionOID,opc.PriceListDate")
                .Append(",NULLIF(opc.DealerNetPrice, '0') AS DealerNetPrice")
                .Append(",NULLIF(opc.DealerNetPriceVAT, '0') AS DealerNetPriceVAT")
                .Append(",NULLIF(opc.RetailPrice, '0') AS RetailPrice")
                .Append(",NULLIF(opc.RetailPriceVAT, '0') AS RetailPriceVAT")
                .Append(",NULLIF(opc.TaxAmount1, '0') AS TaxAmount1")
                .Append(",NULLIF(opc.TaxAmount2, '0') AS TaxAmount2")
                .Append(",NULLIF(opc.TaxAmount3, '0') AS TaxAmount3")
                .Append(",NULLIF(opc.TaxPercentage1, '0') AS TaxPercentage1")
                .Append(",NULLIF(opc.TaxPercentage2, '0') AS TaxPercentage2")
                .Append(",NULLIF(opc.TaxPercentage3, '0') AS TaxPercentage3")
                .Append(", opc.CountryOID,opc.ModelYearOID,opc.CurrencyOID ")
                .Append("from `option` op, optiongroup opg , modeledition  me, optionprice opc , modelyear my, country cou, currency cur, model mo , make m ")
                .Append("where op.GroupOID = opg.OID  and opc.OptionOID = op.OID ")
                .Append("AND opc.ModelEditionOID = me.OID ")
                .Append("and opc.ModelYearOID = my.OID ")
                .Append("and opc.CountryOID = cou.OID ")
                .Append("and opc.CurrencyOID = cur.OID ")
                .Append("and mo.OID = me.ModelOID ")
                .Append("AND m.OID = mo.MakeOID")
                ;

            var cond = string.Empty;
            // Model
            cond += " " + string.Format("AND mo.OID = '{0}'", string.IsNullOrEmpty(modelOid) ? string.Empty : modelOid);
            // Group
            cond += " " + string.Format("AND opg.OID = '{0}'", string.IsNullOrEmpty(groupOid) ? string.Empty : groupOid);
            // Model Year
            cond += " " + string.Format("AND opc.ModelYearOID = '{0}'", string.IsNullOrEmpty(modelYearOid) ? string.Empty : modelYearOid);
            // Country
            cond += " " + string.Format("AND opc.CountryOID = '{0}'", string.IsNullOrEmpty(countryOid) ? string.Empty : countryOid);
            // Currency
            cond += " " + string.Format("AND opc.CurrencyOID = '{0}'", string.IsNullOrEmpty(currencyOid) ? string.Empty : currencyOid);
            // Pricelist date
            cond += " " + string.Format("AND opc.PriceListDate = STR_TO_DATE('{0}', '%Y.%m.%d')", string.IsNullOrEmpty(pricelistDate) ? string.Empty : pricelistDate);
            //make
            cond += " " + string.Format(" AND m.OID = '{0}' ", string.IsNullOrEmpty(makeOid) ? string.Empty : makeOid);

            sb.Append(cond).Append("GROUP BY opc.OptionOID,opc.ModelYearOID,opc.PriceListDate,opc.CountryOID,opc.CurrencyOID,opc.DealerNetPrice,opc.DealerNetPriceVAT,opc.RetailPrice,opc.RetailPriceVAT,")
                .Append("opc.TaxAmount1,opc.TaxAmount2,opc.TaxAmount3,opc.TaxPercentage1,opc.TaxPercentage2,opc.TaxPercentage3,op.BaseName,op.Explanation,op.OptionCode,op.GroupOID ")
                .Append(" ORDER BY op.BaseName ASC ");

            var query = sb.ToString();
            var db = new clsDb();
            var dt = db.GetDataTable(query);

            return dt;
        }

        public static DataTable GetOptionPricelist(int makeOid, int modelYearOid, int langOid, DateTime pricelistDate)
        {
            var query = @"SELECT
	                        m.`NAME` AS Make,
	                        mo.`NAME` AS Model,
	                        me.`NAME` AS Edition,
	                        opg.`NAME` AS 'Group',
	                        opn.`Name` AS 'Language',
	                        op.BaseName,
	                        op.Explanation,
	                        op.OptionCode AS CODE,
	                        op.GroupOID,
	                        opc.OptionOID,
	                        opc.ModelEditionOID,
	                        opc.PriceListDate,
	                        NULLIF(opc.DealerNetPrice, '0') AS DealerNetPrice,
	                        NULLIF(opc.DealerNetPriceVAT, '0') AS DealerNetPriceVAT,
	                        NULLIF(opc.RetailPrice, '0') AS RetailPrice,
	                        NULLIF(opc.RetailPriceVAT, '0') AS RetailPriceVAT,
	                        NULLIF(opc.TaxAmount1, '0') AS TaxAmount1,
	                        NULLIF(opc.TaxAmount2, '0') AS TaxAmount2,
	                        NULLIF(opc.TaxAmount3, '0') AS TaxAmount3,
	                        NULLIF(opc.TaxPercentage1, '0') AS TaxPercentage1,
	                        NULLIF(opc.TaxPercentage2, '0') AS TaxPercentage2,
	                        NULLIF(opc.TaxPercentage3, '0') AS TaxPercentage3,
	                        opc.CountryOID,
	                        opc.ModelYearOID,
	                        opc.CurrencyOID,
	                        opc.Description
                        FROM
	                        `option` op
                        INNER JOIN optiongroup opg ON op.GroupOID = opg.OID
                        INNER JOIN optionprice opc ON opc.OptionOID = op.OID
                        INNER JOIN modeledition me ON opc.ModelEditionOID = me.OID
                        INNER JOIN modelyear my ON opc.ModelYearOID = my.OID
                        INNER JOIN country cou ON opc.CountryOID = cou.OID
                        INNER JOIN currency cur ON opc.CurrencyOID = cur.OID
                        INNER JOIN model mo ON me.ModelOID = mo.OID
                        INNER JOIN make m ON mo.MakeOID = m.OID
                        LEFT JOIN optionname opn ON opn.OptionOID = op.OID
                        AND opn.LanguageOID = {3}
                        WHERE
	                        m.OID = {0}
	                        AND my.OID = {1}
	                        AND opc.PriceListDate = STR_TO_DATE('{2}', '%Y.%m.%d')
                        GROUP BY
	                        opc.OptionOID,
	                        opc.ModelEditionOID,
	                        opc.ModelYearOID,
	                        opc.PriceListDate,
	                        opc.CountryOID,
	                        opc.CurrencyOID,
	                        opc.DealerNetPrice,
	                        opc.DealerNetPriceVAT,
	                        opc.RetailPrice,
	                        opc.RetailPriceVAT,
	                        opc.TaxAmount1,
	                        opc.TaxAmount2,
	                        opc.TaxAmount3,
	                        opc.TaxPercentage1,
	                        opc.TaxPercentage2,
	                        opc.TaxPercentage3,
	                        op.BaseName,
	                        op.Explanation,
	                        op.OptionCode,
	                        op.GroupOID,
	                        opc.Description,
	                        m.`NAME`,
	                        mo.`NAME`,
	                        me.`NAME`,
	                        opg.`NAME`,
	                        opn.`NAME`
                        ORDER BY
	                        mo. NAME ASC";
            query = string.Format(query, makeOid, modelYearOid, pricelistDate.ToString(AppConfig.Constants.DEFAULT_DATE_FORMAT), langOid);
            var db = new clsDb();
            var dt = db.GetDataTable(query);

            return dt;
        }
        public static DataTable GetOptionPricelist()
        {
            return GetOptionPricelist(string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty);
        }

        public static DataTable GetOptionPricelistDate()
        {
            // optionprice opc
            var query = "SELECT DISTINCT PriceListDate FROM `optionprice`";
            var db = new clsDb();
            return db.GetDataTable(query);
        }

        public static DataTable GetModelPricelistDate()
        {
            // optionprice opc
            var query = "SELECT DISTINCT PriceListDate FROM `submodelprice`";
            var db = new clsDb();
            return db.GetDataTable(query);
        }


        // Insert to database
        public static bool CreateSubModelPrice(SubModelPrice subModelPrice)
        {
            var db = new clsDb();
            var query = "INSERT INTO `submodelprice` (`SubmodelOID`, `PriceListDate`, `CountryOID`, `CurrencyOID`, `TaxPercentage1`, `TaxPercentage2`, `TaxPercentage3`, `TaxAmount1`, `TaxAmount2`, `TaxAmount3`, `DealerNetPrice`, `DealerNetPriceVAT`, `RetailPrice`, `RetailPriceVAT`) "
                + "VALUES ({0}, STR_TO_DATE('{1}', '%Y-%m-%d'), '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}')";
            query = string.Format(query, subModelPrice.SubModelOid, subModelPrice.PricelistDate.ToString("yyyy-MM-dd"), subModelPrice.CountryOid, subModelPrice.CurrencyOid, Utils.toStringFormat(subModelPrice.TaxPercentage1), Utils.toStringFormat(subModelPrice.TaxPercentage2), Utils.toStringFormat(subModelPrice.TaxPercentage3), Utils.toStringFormat(subModelPrice.TaxAmount1), Utils.toStringFormat(subModelPrice.TaxAmount2), Utils.toStringFormat(subModelPrice.TaxAmount3), Utils.toStringFormat(subModelPrice.DealerNetPrice), Utils.toStringFormat(subModelPrice.DealerNetPriceVAT), Utils.toStringFormat(subModelPrice.RetailPrice), Utils.toStringFormat(subModelPrice.RetailPriceVAT));
            var result = db.RunQuery(query) > 0;
            return result;
        }

        public static int CreateNewSubModelPrice(SubModelPrice subModelPrice)
        {
            var db = new clsDb();
            // insert submodel first
            var query1 = "INSERT INTO `submodel` (`ModelOID`, `ModelYearOID`, `Name`, `ModelCode`, `ModelEditionOID`, `CylinderCapacity`, `EngineType`, `PowerKW`, `GearBoxType`, `CO2EmissionMin`, `CO2EmissionMax`, `CO2EmissionAvg`, `EmissionNorm`, `FuelType`) "
                + "VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}')";
            query1 = string.Format(query1, subModelPrice.ModelOid, subModelPrice.ModelYearOid, subModelPrice.Name, subModelPrice.ModelCode, subModelPrice.ModelEditionOid, subModelPrice.CylinderCapacity, subModelPrice.EngineType, subModelPrice.PowerKW, subModelPrice.GearBoxType, Utils.toStringFormat(subModelPrice.CO2EmissionMin), Utils.toStringFormat(subModelPrice.CO2EmissionMax), Utils.toStringFormat(subModelPrice.CO2EmissionAvg), subModelPrice.EmissionNorm, subModelPrice.FuelType);
            var subModelOid = db.saveModel(query1);
            subModelPrice.SubModelOid = subModelOid;
            // insert submodelprice second
            var query2 = "INSERT INTO `submodelprice` (`SubmodelOID`, `PriceListDate`, `CountryOID`, `CurrencyOID`, `TaxPercentage1`, `TaxPercentage2`, `TaxPercentage3`, `TaxAmount1`, `TaxAmount2`, `TaxAmount3`, `DealerNetPrice`, `DealerNetPriceVAT`, `RetailPrice`, `RetailPriceVAT`) "
                + "VALUES ('{0}', STR_TO_DATE('{1}', '%Y-%m-%d'), '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}')";
            query2 = string.Format(query2, subModelPrice.SubModelOid, subModelPrice.PricelistDate.ToString("yyyy-MM-dd"), subModelPrice.CountryOid, subModelPrice.CurrencyOid, Utils.toStringFormat(subModelPrice.TaxPercentage1), Utils.toStringFormat(subModelPrice.TaxPercentage2), Utils.toStringFormat(subModelPrice.TaxPercentage3), Utils.toStringFormat(subModelPrice.TaxAmount1), Utils.toStringFormat(subModelPrice.TaxAmount2), Utils.toStringFormat(subModelPrice.TaxAmount3), Utils.toStringFormat(subModelPrice.DealerNetPrice), Utils.toStringFormat(subModelPrice.DealerNetPriceVAT), Utils.toStringFormat(subModelPrice.RetailPrice), Utils.toStringFormat(subModelPrice.RetailPriceVAT));
            var result = db.saveModel(query2);
            return result;
        }


        public static int UpdateSubModelPrice(SubModelPrice subModelPrice)
        {
            var db = new clsDb();
            // insert submodel first
            var query1 = "UPDATE `submodel` "
                + " SET `ModelYearOID` = '{0}', `Name` = '{1}', `ModelCode` = '{2}', `ModelEditionOID` = '{3}', `CylinderCapacity` = '{4}', `EngineType` = '{5}', "
                + " `PowerKW` = '{6}', `GearBoxType` = '{7}', `CO2EmissionMin` = '{8}', `CO2EmissionMax` = '{9}', `CO2EmissionAvg` = '{10}', `EmissionNorm` = '{11}', "
                + " `FuelType` = '{12}' "
            + "WHERE OID = {13}";
            query1 = string.Format(query1, subModelPrice.ModelYearOid, subModelPrice.Name, subModelPrice.ModelCode, subModelPrice.ModelEditionOid,
                Utils.toStringFormat(subModelPrice.CylinderCapacity), subModelPrice.EngineType, Utils.toStringFormat(subModelPrice.PowerKW), subModelPrice.GearBoxType, Utils.toStringFormat(subModelPrice.CO2EmissionMin),
               Utils.toStringFormat(subModelPrice.CO2EmissionMax), Utils.toStringFormat(subModelPrice.CO2EmissionAvg), subModelPrice.EmissionNorm, subModelPrice.FuelType, subModelPrice.SubModelOid);
            db.RunQuery(query1);
            // insert submodelprice second
            var query2 = "UPDATE `submodelprice` "
                + " SET `TaxPercentage1` = '{0}', `TaxPercentage2` = '{1}', `TaxPercentage3` = '{2}',"
                + " `TaxAmount1` = '{3}', `TaxAmount2` = '{4}', `TaxAmount3` = '{5}', `DealerNetPrice` = '{6}', `DealerNetPriceVAT` = '{7}',"
                + " `RetailPrice` = '{8}', `RetailPriceVAT` = '{9}' "
            + "WHERE OID = {10}";
            query2 = string.Format(query2, Utils.toStringFormat(subModelPrice.TaxPercentage1), Utils.toStringFormat(subModelPrice.TaxPercentage2), Utils.toStringFormat(subModelPrice.TaxPercentage3),
                 Utils.toStringFormat(subModelPrice.TaxAmount1), Utils.toStringFormat(subModelPrice.TaxAmount2), Utils.toStringFormat(subModelPrice.TaxAmount3), Utils.toStringFormat(subModelPrice.DealerNetPrice), Utils.toStringFormat(subModelPrice.DealerNetPriceVAT),
                 Utils.toStringFormat(subModelPrice.RetailPrice), Utils.toStringFormat(subModelPrice.RetailPriceVAT), subModelPrice.OID);
            var result = db.RunQuery(query2);
            return result;
        }


        public static int InsertOptionModel(List<Option> ops)
        {
            int count = -1;
            string sql = "INSERT INTO `option` (GroupOID, OptionCode, BaseName,Explanation) VALUES";
            foreach (Option op in ops)
            {
                sql += "(" + op.GroupOID + ",'" + op.OptionCode + "','" + op.BaseNameSafeSQL + "','" + op.ExplanationSafeSQL + "'),";
            }
            sql = sql.Remove(sql.LastIndexOf(","));
            var db = new clsDb();
            count = db.RunQuery(sql);

            return count;
        }
        // Update datasource
        public static void UpdateDataSource(DataTable table, params string[] tableNames)
        {
            var db = new clsDb();
            if (tableNames != null && tableNames.Length > 0)
            {
                foreach (var tableName in tableNames)
                {
                    var t = table.Copy();
                    //t.AcceptChanges();
                    db.UpdateDatatable(t, tableName);
                }
            }
        }


        public static int DeleteByOid(String tableName, int oid)
        {
            var db = new clsDb();
            if (!string.IsNullOrEmpty(tableName))
            {
                var query = string.Format("DELETE FROM `{0}` WHERE OID = {1} LIMIT 1", tableName, oid);
                return db.RunQuery(query);
            }
            return 0;
        }

        public static int DeleteByOid(String tableName, string oids)
        {
            var db = new clsDb();
            if (!string.IsNullOrEmpty(tableName) && !string.IsNullOrEmpty(oids))
            {
                var query = string.Format("DELETE FROM `{0}` WHERE OID IN ({1})", tableName, oids);
                return db.RunQuery(query);
            }
            return 0;
        }


        public static int CountOption(string OptionCode, int GroupOID)
        {
            int count = -1;

            string sql = "SELECT COUNT(1) FROM `option` WHERE optioncode= '{0}' AND groupoid = {1} ";
            sql = string.Format(sql, OptionCode, GroupOID);
            var db = new clsDb();
            count = db.countRecord(sql);
            return count;
        }

        public static int InsertOption(Option op)
        {
            int ret = -1;
            try
            {
                // int ret = -1;
                int count = -1;
                count = CountOption(op.OptionCode, op.GroupOID);

                if (count == 0)
                {
                    string sql = "INSERT INTO `option` (GroupOID, OptionCode, BaseName,Explanation) VALUES ({0},'{1}','{2}','{3}')";

                    //sql += "(" + op.GroupOID + ",'" + op.OptionCode + "','" + op.BaseName + "','" + op.Explanation + "'),";
                    sql = string.Format(sql, op.GroupOID, op.OptionCode, op.BaseNameSafeSQL, op.ExplanationSafeSQL);

                    var db = new clsDb();
                    int oid = -1;

                    oid = db.saveModel(sql);
                    if (oid != -1)
                    {
                        if (op.opns != null && op.opns.Count > 0)
                        {
                            string sqlBulk = "INSERT INTO `optionname` (OptionOID, LanguageOID, `Name`) VALUES ";
                            foreach (OptionName opn in op.opns)
                            {
                                //string insertOptionName = "INSERT INTO `optionname` (OptionOID, LanguageOID, `Name`) VALUES({0},{1},'{2}')";
                                //insertOptionName = string.Format(insertOptionName, oid, opn.LanguageOID, opn.Name);
                                sqlBulk += "(" + oid.ToString() + "," + opn.LanguageOID + ",'" + opn.Name + "'),";
                            }
                            sqlBulk = sqlBulk.Remove(sqlBulk.LastIndexOf(","));
                            db.saveModel(sqlBulk);
                        }

                    }
                    ret = 1;

                }
                else
                {
                    log.Error("This item existed in DB !");
                    ret = -2;

                }
                return ret;

            }
            catch (Exception ex)
            {
                log.Error(ex.Message);
                ret = -1;
                return ret;
            }

        }
        public static Option CreateOptionModel(Option op)
        {
            string sql = "INSERT INTO `option` (GroupOID, OptionCode, BaseName,Explanation) VALUES ({0},'{1}','{2}','{3}')";
            //sql += "(" + op.GroupOID + ",'" + op.OptionCode + "','" + op.BaseName + "','" + op.Explanation + "'),";
            sql = string.Format(sql, op.GroupOID, op.OptionCode, op.BaseNameSafeSQL, op.ExplanationSafeSQL);

            var db = new clsDb();
            int oid = -1;

            oid = db.saveModel(sql);

            //if (oid != -1)
            op.OID = oid;
            return op;
        }

        public static OptionGroup CreateOptionGroup(OptionGroup g)
        {
            try
            {
                var sql = "INSERT INTO `optiongroup` (MakeOID, Name) VALUES ({0},'{1}')";
                sql = string.Format(sql, g.MakeOID, g.Name);

                var db = new clsDb();
                int oid = db.saveModel(sql);

                if (oid != -1)
                    g.OID = oid;
                return g;
            }
            catch (Exception ex)
            {
                log.Error(ex.Message);
                throw ex;
            }
        }

        public static OptionName CreateOptionName(OptionName on)
        {
            try
            {
                var sql = "INSERT INTO `optionname` (OptionOID, LanguageOID, Name) VALUES ({0},'{1}', '{2}')";
                sql = string.Format(sql, on.OptionOID, on.LanguageOID, on.Name);

                var db = new clsDb();
                int oid = db.saveModel(sql);

                if (oid != -1)
                    on.OID = oid;
                return on;
            }
            catch (Exception ex)
            {
                log.Error(ex.Message);
                throw ex;
            }
        }


        public static int CountOptionName(int OptionOID, int LangOID)
        {
            int count = -1;

            string sql = "SELECT COUNT(1) FROM optionname WHERE optionOID = {0} AND languageOID = {1} ";
            sql = string.Format(sql, OptionOID, LangOID);
            var db = new clsDb();
            count = db.countRecord(sql);
            return count;
        }
        public static int UpdateOption(Option op)
        {
            //System.Windows.Forms.MessageBox.Show(op.OID.ToString());
            try
            {
                int cUpdate = -1;
                string sqlUpdateOption = "UPDATE `option` SET GROUPOID = {0} , OPTIONCODE = '{1}' , BASENAME = '{2}' , EXPLANATION = '{3}' WHERE OID = {4}";
                sqlUpdateOption = string.Format(sqlUpdateOption, op.GroupOID, op.OptionCode, op.BaseNameSafeSQL, op.ExplanationSafeSQL, op.OID);
                var db = new clsDb();
                cUpdate = db.RunQuery(sqlUpdateOption);
                if (op.opns != null)
                {

                    if (op.opns.Count > 0)
                    {
                        //if exist UPDATE else INSERT
                        foreach (OptionName opn in op.opns)
                        {
                            //count in db
                            int count = -1;
                            count = DBAccess.CountOptionName(opn.OptionOID, opn.LanguageOID);
                            string sqlUpdateOptionName = "";
                            if (count != -1 & count != 0) // exist
                            {
                                sqlUpdateOptionName = "UPDATE optionname SET `NAME` = '{0}' WHERE OPTIONOID ={1} AND  LANGUAGEOID = {2}";
                                sqlUpdateOptionName = string.Format(sqlUpdateOptionName, opn.Name, opn.OptionOID, opn.LanguageOID);
                            }
                            else
                            {
                                sqlUpdateOptionName = "INSERT INTO `optionname` (OptionOID, LanguageOID, `Name`) VALUES({0},{1},'{2}')";
                                sqlUpdateOptionName = string.Format(sqlUpdateOptionName, op.OID, opn.LanguageOID, opn.Name);
                            }
                            db.RunQuery(sqlUpdateOptionName);

                        }
                    }
                }
                return cUpdate;

            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message, "Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                log.Error(ex.Message);
                return -1;
                throw ex;
            }

        }

        public static int LangCodeToOID(string code)
        {
            int ret = -1;
            string sql = "SELECT oid FROM Language where Code like '{0}'";
            sql = string.Format(sql, code);
            var db = new clsDb();
            DataTable dt = new DataTable();
            dt = db.GetDataTable(sql);
            if (dt != null)
                if (dt.Rows.Count > 0)
                {
                    ret = dt.Rows[0].Field<int>("OID");
                }
            return ret;

        }

        public static int deleteTransaction(string id)
        {
            int count = -1;
            string sqlOptionDel = "DELETE FROM `option` WHERE OID IN ({0})";
            sqlOptionDel = string.Format(sqlOptionDel, id);
            string sqlOptionNameDel = "DELETE FROM `optionname` WHERE optionoid IN ({0})";
            sqlOptionNameDel = string.Format(sqlOptionNameDel, id);

            // System.Windows.Forms.MessageBox.Show(sqlOptionDel);
            //System.Windows.Forms.MessageBox.Show(sqlOptionNameDel);
            var db = new clsDb();
            int val = -1;
            val = db.RunQuery(sqlOptionNameDel);
            if (val != -1)
                count = db.RunQuery(sqlOptionDel);

            return count;
        }
        public static void ToCSV_bk(DataTable dtDataTable, string strFilePath, string LangCode)
        {
            try
            {
                StreamWriter sw = new StreamWriter(strFilePath, false, Encoding.UTF8);
                //headers  
                for (int i = 1; i < dtDataTable.Columns.Count; i++)
                {
                    if (i < 5 || (i >= 5 & dtDataTable.Columns[i].ColumnName.Equals(LangCode)))
                    {
                        sw.Write(dtDataTable.Columns[i]);
                        if (i < dtDataTable.Columns.Count - 1)
                        {
                            sw.Write(",");
                        }
                    }
                }
                sw.Write(sw.NewLine);
                foreach (DataRow dr in dtDataTable.Rows)
                {
                    for (int i = 1; i < dtDataTable.Columns.Count; i++)
                    {
                        if (i < 5 || (i >= 5 & dtDataTable.Columns[i].ColumnName.Equals(LangCode)))
                        {
                            if (!Convert.IsDBNull(dr[i]))
                            {
                                string value = dr[i].ToString();
                                if (value.Contains(','))
                                {
                                    value = String.Format("\"{0}\"", value);
                                    sw.Write(value);
                                }
                                else
                                {
                                    sw.Write(dr[i].ToString());
                                }
                            }
                            if (i < dtDataTable.Columns.Count - 1)
                            {
                                sw.Write(",");
                            }
                        }
                    }
                    sw.Write(sw.NewLine);
                }
                sw.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                throw ex;
            }
        }

        public static void ToCSV(DataGridView gridIn, string outputFile, string LangCode, string sep)
        {
            try
            {
                // StreamWriter sw = new StreamWriter(strFilePath, false, Encoding.UTF8);
                if (gridIn.RowCount > 0)
                {
                    string value = "";
                    DataGridViewRow dr = new DataGridViewRow();
                    StreamWriter swOut = new StreamWriter(outputFile, false, Encoding.UTF8);

                    //write header rows to csv
                    for (int i = 1; i <= gridIn.Columns.Count - 1; i++)
                    {
                        if (gridIn.Columns[i].Visible)
                        {
                            if ((i > 0 && i < 5) || (i >= 5 && gridIn.Columns[i].Name.Equals(LangCode)))
                            {
                                swOut.Write(gridIn.Columns[i].HeaderText);
                                swOut.Write(sep);
                            }
                        }
                    }

                    swOut.WriteLine();

                    //write DataGridView rows to csv
                    for (int j = 0; j <= gridIn.Rows.Count - 1; j++)
                    {
                        if (j > 0)
                        {
                            swOut.WriteLine();
                        }

                        dr = gridIn.Rows[j];

                        for (int i = 1; i <= gridIn.Columns.Count - 1; i++)
                        {
                            if (gridIn.Columns[i].Visible)
                            {
                                if ((gridIn.Columns[i].Visible && i < 5) || (i >= 5 && gridIn.Columns[i].Name.Equals(LangCode)))
                                {

                                    if (gridIn.Columns[i].GetType() == typeof(DataGridViewComboBoxColumn))
                                    {
                                        DataGridViewComboBoxCell dgvcmbcell = dr.Cells[i] as DataGridViewComboBoxCell;
                                        value = dgvcmbcell.EditedFormattedValue.ToString();
                                    }
                                    else
                                        value = dr.Cells[i].Value == null ? "" : dr.Cells[i].Value.ToString();

                                    //replace comma's with spaces
                                    value = value.Replace(',', ' ');

                                    //replace embedded newlines with spaces
                                    value = value.Replace(Environment.NewLine, " ");

                                    swOut.Write(value);
                                    swOut.Write(sep);
                                }
                            }
                        }
                    }
                    swOut.Close();
                }
            }
            catch (Exception ex)
            {
                log.Error(ex.Message);
                MessageBox.Show(ex.Message);
            }
        }





        public static List<string[]> parseCSV(string path, string sep)
        {
            try
            {
                List<string[]> parsedData = new List<string[]>();

                TextReader tr = new StreamReader(path, Encoding.Default);

                var parser = new CsvParser(tr);
                parser.Configuration.HasHeaderRecord = false;

                parser.Configuration.Delimiter = sep;
                // parser.Configuration.Encoding = Encoding.UTF32;
                while (true)
                {
                    var row = parser.Read();

                    if (row == null)
                    {
                        break;
                    }
                    parsedData.Add(row);

                }

                //using (StreamReader readFile = new StreamReader(path, Encoding.Default))
                //{
                //    string line = "";
                //    string[] row = null;

                //    while (!readFile.EndOfStream)
                //    {
                //        line = readFile.ReadLine();
                //        if (!string.IsNullOrEmpty(line))
                //        {
                //            if (line.EndsWith(sep))
                //                line = line.Remove(line.LastIndexOf(sep));
                //            row = line.Split(sep.ToCharArray());
                //            parsedData.Add(row);
                //        }
                //    }
                //}

                return parsedData;
            }
            catch (Exception ex)
            {
                log.Error(ex.Message, ex);
                throw ex;
            }
        }


        public static int CreateOptionPrice(OptionPrice op)
        {
            int val = -1;
            string sql = "INSERT INTO optionprice (OptionOID,ModelEditionOID,ModelYearOID,PriceListDate,CountryOID,CurrencyOID,"
                + "TaxPercentage1,TaxPercentage2,TaxPercentage3,TaxAmount1,TaxAmount2,TaxAmount3,DealerNetPrice,"
                + "RetailPrice,DealerNetPriceVAT,RetailPriceVAT,Description)"
                + "VALUES ('{0}','{1}','{2}',STR_TO_DATE('{3}', '%Y.%m.%d'),'{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}')"; //%d/%m/%Y
            sql = string.Format(sql, op.OptionOID, op.ModelEditionOID, op.ModelYearOID, op.PriceListDate.ToString(AppConfig.Constants.DEFAULT_DATE_FORMAT),
                op.CountryOID, op.CurrencyOID,
                string.IsNullOrEmpty(op.TaxPercentage1) ? "NULL" : Utils.CommaToDot(op.TaxPercentage1),
                string.IsNullOrEmpty(op.TaxPercentage2) ? "NULL" : Utils.CommaToDot(op.TaxPercentage2),
                string.IsNullOrEmpty(op.TaxPercentage3) ? "NULL" : Utils.CommaToDot(op.TaxPercentage3),
                string.IsNullOrEmpty(op.TaxAmount1) ? "NULL" : Utils.CommaToDot(op.TaxAmount1),
                string.IsNullOrEmpty(op.TaxAmount2) ? "NULL" : Utils.CommaToDot(op.TaxAmount2),
                string.IsNullOrEmpty(op.TaxAmount3) ? "NULL" : Utils.CommaToDot(op.TaxAmount3),
                string.IsNullOrEmpty(op.DealerNetPrice) ? "NULL" : Utils.CommaToDot(op.DealerNetPrice),
                string.IsNullOrEmpty(op.RetailPrice) ? "NULL" : Utils.CommaToDot(op.RetailPrice),
                string.IsNullOrEmpty(op.DealerNetPriceVAT) ? "NULL" : Utils.CommaToDot(op.DealerNetPriceVAT),
                string.IsNullOrEmpty(op.RetailPriceVAT) ? "NULL" : Utils.CommaToDot(op.RetailPriceVAT),
                string.IsNullOrEmpty(op.Description) ? null : op.Description);
            var db = new clsDb();
            val = db.RunQuery(sql);
            return val;
        }

        public static int CreateOptionPrice(List<OptionPrice> ops)
        {
            if (ops != null && ops.Count > 0)
            {
                var sql = "INSERT INTO optionprice (OptionOID,ModelEditionOID,ModelYearOID,PriceListDate,CountryOID,CurrencyOID,"
                    + "TaxPercentage1,TaxPercentage2,TaxPercentage3,TaxAmount1,TaxAmount2,TaxAmount3,DealerNetPrice,"
                    + "RetailPrice,DealerNetPriceVAT,RetailPriceVAT,Description)"
                    + "VALUES ";
                foreach (var op in ops)
                {
                    var val = "('{0}','{1}','{2}',STR_TO_DATE('{3}', '%Y.%m.%d'),'{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}'),";
                    sql += string.Format(val, op.OptionOID, op.ModelEditionOID, op.ModelYearOID, op.PriceListDate.ToString(AppConfig.Constants.DEFAULT_DATE_FORMAT),
                    op.CountryOID, op.CurrencyOID,
                    string.IsNullOrEmpty(op.TaxPercentage1) ? "NULL" : Utils.CommaToDot(op.TaxPercentage1),
                    string.IsNullOrEmpty(op.TaxPercentage2) ? "NULL" : Utils.CommaToDot(op.TaxPercentage2),
                    string.IsNullOrEmpty(op.TaxPercentage3) ? "NULL" : Utils.CommaToDot(op.TaxPercentage3),
                    string.IsNullOrEmpty(op.TaxAmount1) ? "NULL" : Utils.CommaToDot(op.TaxAmount1),
                    string.IsNullOrEmpty(op.TaxAmount2) ? "NULL" : Utils.CommaToDot(op.TaxAmount2),
                    string.IsNullOrEmpty(op.TaxAmount3) ? "NULL" : Utils.CommaToDot(op.TaxAmount3),
                    string.IsNullOrEmpty(op.DealerNetPrice) ? "NULL" : Utils.CommaToDot(op.DealerNetPrice),
                    string.IsNullOrEmpty(op.RetailPrice) ? "NULL" : Utils.CommaToDot(op.RetailPrice),
                    string.IsNullOrEmpty(op.DealerNetPriceVAT) ? "NULL" : Utils.CommaToDot(op.DealerNetPriceVAT),
                    string.IsNullOrEmpty(op.RetailPriceVAT) ? "NULL" : Utils.CommaToDot(op.RetailPriceVAT),
                    string.IsNullOrEmpty(op.Description) ? null : op.Description);
                }
                var db = new clsDb();
                sql = sql.Remove(sql.Length - 1);
                var result = db.RunQuery(sql);
                return result;

            }
            else
            {
                return 0;
            }

        }

        public static int UpdateOptionPrice(OptionPrice op)
        {
            int val = -1;
            if (op.OID != -1)
            {
                string sql = "UPDATE optionprice SET TaxPercentage1='{0}',TaxPercentage2='{1}',TaxPercentage3='{2}',TaxAmount1='{3}',TaxAmount2='{4}',TaxAmount3='{5}',DealerNetPrice='{6}',"
                       + "RetailPrice='{7}',DealerNetPriceVAT='{8}',RetailPriceVAT='{9}', ModelEditionOID='{10}' WHERE OID ='{11}'";

                //+ "VALUES ({0},{1},{2},STR_TO_DATE('{3}', '%Y.%m.%d'),{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},'{16}')";
                sql = string.Format(sql,
                    string.IsNullOrEmpty(op.TaxPercentage1) ? "NULL" : Utils.CommaToDot(op.TaxPercentage1),
                    string.IsNullOrEmpty(op.TaxPercentage2) ? "NULL" : Utils.CommaToDot(op.TaxPercentage2),
                    string.IsNullOrEmpty(op.TaxPercentage3) ? "NULL" : Utils.CommaToDot(op.TaxPercentage3),
                    string.IsNullOrEmpty(op.TaxAmount1) ? "NULL" : Utils.CommaToDot(op.TaxAmount1),
                    string.IsNullOrEmpty(op.TaxAmount2) ? "NULL" : Utils.CommaToDot(op.TaxAmount2),
                    string.IsNullOrEmpty(op.TaxAmount3) ? "NULL" : Utils.CommaToDot(op.TaxAmount3),
                    string.IsNullOrEmpty(op.DealerNetPrice) ? "NULL" : Utils.CommaToDot(op.DealerNetPrice),
                    string.IsNullOrEmpty(op.RetailPrice) ? "NULL" : Utils.CommaToDot(op.RetailPrice),
                    string.IsNullOrEmpty(op.DealerNetPriceVAT) ? "NULL" : Utils.CommaToDot(op.DealerNetPriceVAT),
                    string.IsNullOrEmpty(op.RetailPriceVAT) ? "NULL" : Utils.CommaToDot(op.RetailPriceVAT),
                    op.ModelEditionOID, op.OID);
                //MessageBox.Show(sql);
                var db = new clsDb();
                val = db.RunQuery(sql);
            }
            return val;


        }

        public static int deleteOptionPrice(string id)
        {
            if (string.IsNullOrEmpty(id))
            {
                return 0;
            }
            int val = -1;
            string sql = "DELETE FROM optionprice WHERE OID in ({0})";
            sql = string.Format(sql, id);
            var db = new clsDb();
            val = db.RunQuery(sql);
            return val;
        }


        public static string getValueLang(int optionnameoid, String LangOid)
        {
            string val = "";
            string sql = "SELECT optionname  where OptionOID = {0} and Language = '{1}'";
            sql = string.Format(sql, optionnameoid, LangOid);
            var db = new clsDb();
            DataTable dt = new DataTable();
            dt = db.GetDataTable(sql);
            if (dt != null)
                if (dt.Rows.Count > 0)
                    val = dt.Rows[0][1].ToString();


            return val;
        }

        public static string getBaseNameByLangOid(string langOid, string optionoid)
        {
            string val = "";

            string sql = "Select`name` from optionname where LanguageOID ={0} and OptionOID = {1}";
            sql = string.Format(sql, langOid, optionoid);
            //  MessageBox.Show(sql);
            var db = new clsDb();
            DataTable dt = db.GetDataTable(sql);
            if (dt != null)
                if (dt.Rows.Count > 0)
                    val = dt.Rows[0][0].ToString();

            return val;
        }

        public static void OptionPriceExportToCSV(DataGridView gridIn, string outputFile, string LangOid)
        {
            try
            {
                // StreamWriter sw = new StreamWriter(strFilePath, false, Encoding.UTF8);
                if (gridIn.RowCount > 0)
                {
                    string value = "";
                    DataGridViewRow dr = new DataGridViewRow();
                    StreamWriter swOut = new StreamWriter(outputFile, false, Encoding.UTF8);

                    //write header rows to csv
                    for (int i = 0; i <= gridIn.Columns.Count - 1; i++)
                    {
                        if (i > 0 && gridIn.Columns[i].Visible)
                        {
                            swOut.Write(",");
                        }
                        if (gridIn.Columns[i].Visible)
                            swOut.Write(gridIn.Columns[i].HeaderText);
                    }

                    swOut.WriteLine();

                    //write DataGridView rows to csv
                    for (int j = 0; j <= gridIn.Rows.Count - 1; j++)
                    {
                        if (j > 0)
                        {
                            swOut.WriteLine();
                        }

                        dr = gridIn.Rows[j];
                        string optionOid = "";

                        for (int i = 0; i <= gridIn.Columns.Count - 1; i++)
                        {
                            if (i > 0 && gridIn.Columns[i].Visible)
                            {
                                swOut.Write(",");
                            }
                            if (!gridIn.Columns[i].Visible)
                            {
                                if (gridIn.Columns[i].Name.Equals(FieldNames.OPTION_OID))
                                {
                                    optionOid = dr.Cells[i].Value.ToString();

                                }
                            }
                            if (gridIn.Columns[i].Visible)
                            {

                                if (gridIn.Columns[i].GetType() == typeof(DataGridViewComboBoxColumn))
                                {
                                    DataGridViewComboBoxCell dgvcmbcell = dr.Cells[i] as DataGridViewComboBoxCell;
                                    value = dgvcmbcell.EditedFormattedValue.ToString();

                                }
                                else if (gridIn.Columns[i].Name.Equals(FieldNames.BASE_NAME))
                                {
                                    value = getBaseNameByLangOid(LangOid, optionOid);

                                    //  value = dr.Cells[i].Value == null ? "" : dr.Cells[i].Value.ToString(); 

                                }

                                else
                                    value = dr.Cells[i].Value == null ? "" : dr.Cells[i].Value.ToString();
                                //replace comma's with spaces
                                value = value.Replace(',', ' ');
                                //replace embedded newlines with spaces
                                value = value.Replace(Environment.NewLine, " ");

                                swOut.Write(value);
                            }
                        }
                    }
                    swOut.Close();
                }
            }
            catch (Exception ex)
            {
                log.Error(ex.Message, ex);
                throw ex;
            }
        }


        public static void ModelPriceExportToCSV(DataGridView gridIn, string outputFile, string LangOid)
        {
            try
            {
                // StreamWriter sw = new StreamWriter(strFilePath, false, Encoding.UTF8);
                if (gridIn.RowCount > 0)
                {
                    string value = "";
                    DataGridViewRow dr = new DataGridViewRow();
                    StreamWriter swOut = new StreamWriter(outputFile, false, Encoding.UTF8);

                    foreach (var item in gridIn.Columns)
                    {
                        var gvColumn = item as DataGridViewColumn;
                        Console.WriteLine(gvColumn.HeaderText);
                    }

                    //write header rows to csv
                    for (int i = 0; i <= gridIn.Columns.Count - 1; i++)
                    {
                        if (gridIn.Columns[i].Visible)
                        {
                            swOut.Write(gridIn.Columns[i].HeaderText);
                            swOut.Write(",");
                        }
                    }

                    swOut.WriteLine();

                    //write DataGridView rows to csv
                    for (int j = 0; j <= gridIn.Rows.Count - 1; j++)
                    {
                        if (j > 0)
                        {
                            swOut.WriteLine();
                        }

                        dr = gridIn.Rows[j];
                        string optionOid = "";

                        for (int i = 0; i <= gridIn.Columns.Count - 1; i++)
                        {
                            if (!gridIn.Columns[i].Visible)
                            {
                                if (gridIn.Columns[i].Name.Equals(FieldNames.OPTION_OID))
                                {
                                    optionOid = dr.Cells[i].Value.ToString();

                                }
                            }
                            else
                            {

                                if (gridIn.Columns[i].GetType() == typeof(DataGridViewComboBoxColumn))
                                {
                                    DataGridViewComboBoxCell dgvcmbcell = dr.Cells[i] as DataGridViewComboBoxCell;
                                    value = dgvcmbcell.EditedFormattedValue.ToString();

                                }
                                else if (gridIn.Columns[i].Name.Equals(FieldNames.BASE_NAME))
                                {
                                    value = getBaseNameByLangOid(LangOid, optionOid);

                                    //  value = dr.Cells[i].Value == null ? "" : dr.Cells[i].Value.ToString(); 

                                }

                                else
                                    value = dr.Cells[i].Value == null ? "" : dr.Cells[i].Value.ToString();
                                //replace comma's with spaces
                                value = value.Replace(',', ' ');
                                //replace embedded newlines with spaces
                                value = value.Replace(Environment.NewLine, " ");

                                swOut.Write(value);
                                swOut.Write(",");
                            }
                        }
                    }
                    swOut.Close();
                }
            }
            catch (Exception ex)
            {
                log.Error(ex.Message, ex);
                MessageBox.Show(ex.Message);
            }
        }


        public static int getGroupOidFromName(string name)
        {
            int ret = -1;
            string sql = "Select oid from optiongroup where `name` like '{0}'";
            sql = string.Format(sql, name);
            DataTable dt = new DataTable();
            var db = new clsDb();
            dt = db.GetDataTable(sql);
            if (dt != null)
                if (dt.Rows.Count > 0)
                { ret = (int)dt.Rows[0][0]; }

            return ret;
        }

        public static int GetModelEditionOidByName(string name)
        {
            int ret = -1;
            string sql = "Select oid from modeledition where `name` like '{0}'";
            sql = string.Format(sql, name);
            DataTable dt = new DataTable();
            var db = new clsDb();
            dt = db.GetDataTable(sql);
            if (dt != null)
                if (dt.Rows.Count > 0)
                { ret = (int)dt.Rows[0][0]; }

            return ret;
        }
        public static int InsertOptionName(OptionName op)
        {
            int ret = -1;
            string sql = "INSERT INTO `optionname` (OptionOID, LanguageOID, `Name`) VALUES({0},{1},'{2}')";
            sql = string.Format(sql, op.OptionOID, op.LanguageOID, op.Name);
            var db = new clsDb();
            ret = db.RunQuery(sql);
            return ret;
        }
        public static int GetOidByName(string name, string table)
        {
            int ret = -1;
            string sql = "Select oid from {0} where `name` like '{1}'";
            sql = string.Format(sql, table, name);
            DataTable dt = new DataTable();
            var db = new clsDb();
            dt = db.GetDataTable(sql);
            if (dt != null)
                if (dt.Rows.Count > 0)
                { ret = (int)dt.Rows[0][0]; }

            return ret;
        }

        public static VehicleConfigurator.Model.Model GetModelByName(string name)
        {
            VehicleConfigurator.Model.Model ret = null;
            string sql = "Select * from Model m where m.name like '{0}'";
            sql = string.Format(sql, name);
            var db = new clsDb();

            DataTable dt = new DataTable();
            dt = db.GetDataTable(sql);
            if (dt != null)
            {
                if (dt.Rows.Count > 0)
                {
                    DataRow r = dt.Rows[0];
                    //get model properties
                    foreach (var item in r.ItemArray) // Loop over the items.
                    {
                        ret = new VehicleConfigurator.Model.Model();
                        ret.OID = (int)r[FieldNames.OID];
                        ret.MakeOID = (int)r[FieldNames.OID_MAKE];
                        ret.Name = r[FieldNames.NAME].ToString();
                    }
                    return ret;

                }
                else
                    return null;
            }
            else
                return null;


        }

        public static VehicleConfigurator.Model.Model CreateModel(VehicleConfigurator.Model.Model m)
        {
            try
            {
                var sql = "INSERT INTO `model` (MakeOID,Name) VALUES ({0},'{1}')";
                sql = string.Format(sql, m.MakeOID, m.Name);

                var db = new clsDb();
                int oid = db.saveModel(sql);

                if (oid != -1)
                    m.OID = oid;
                return m;
            }
            catch (Exception ex)
            {
                log.Error(ex.Message);
                throw ex;
            }
        }

        public static ModelYear GetModelYearByName(string name, string makeOid)
        {
            ModelYear ret = null;
            string sql = "select * from modelyear where `name` like '{0}' and makeOID = {1}";
            sql = string.Format(sql, name, makeOid);
            var db = new clsDb();

            DataTable dt = new DataTable();
            dt = db.GetDataTable(sql);
            if (dt != null)
            {
                if (dt.Rows.Count > 0)
                {
                    DataRow r = dt.Rows[0];
                    //get model properties
                    foreach (var item in r.ItemArray) // Loop over the items.
                    {
                        ret = new ModelYear();
                        ret.OID = (int)r[FieldNames.OID];
                        ret.MakeOID = (int)r[FieldNames.OID_MAKE];
                        ret.Name = r[FieldNames.NAME].ToString();
                    }
                    return ret;

                }
                else
                    return null;
            }
            else
                return null;
        }


        public static ModelYear CreateModelYear(ModelYear m)
        {
            try
            {
                var sql = "INSERT INTO ModelYear (MakeOID,Name) VALUES ({0},'{1}')";
                sql = string.Format(sql, m.MakeOID, m.Name);

                var db = new clsDb();
                int oid = db.saveModel(sql);

                if (oid != -1)
                    m.OID = oid;
                return m;
            }
            catch (Exception ex)
            {
                log.Error(ex.Message);
                throw ex;
            }
        }



        public static ModelEdition GetModelEditionByCode(string name, string modeloid)
        {
            ModelEdition ret = null;
            string sql = "select * from modeledition where `name` like '{0}' and modelOID = {1}";
            sql = string.Format(sql, name, modeloid);
            var db = new clsDb();

            DataTable dt = new DataTable();
            dt = db.GetDataTable(sql);
            if (dt != null)
            {
                if (dt.Rows.Count > 0)
                {
                    DataRow r = dt.Rows[0];
                    //get model properties
                    foreach (var item in r.ItemArray) // Loop over the items.
                    {
                        ret = new ModelEdition();
                        ret.OID = (int)r[FieldNames.OID];
                        ret.ModelOID = (int)r[FieldNames.OID_MODEL];
                        ret.Name = r[FieldNames.NAME].ToString();
                    }
                    return ret;

                }
                else
                    return null;
            }
            else
                return null;
        }



        public static ModelEdition CreateModelEdition(ModelEdition m)
        {
            try
            {
                var sql = "INSERT INTO ModelEdition (ModelOID,Name) VALUES ({0},'{1}')";
                sql = string.Format(sql, m.ModelOID, m.Name);

                var db = new clsDb();
                int oid = db.saveModel(sql);

                if (oid != -1)
                    m.OID = oid;
                return m;
            }
            catch (Exception ex)
            {
                log.Error(ex.Message);
                throw ex;
            }
        }



        public static int CreateSubModel(SubModelPrice subModelPrice)
        {
            int result = -1;
            var db = new clsDb();
            var query = "INSERT INTO `submodel` ( `ModelOID`, `ModelYearOID`, `Name`, `ModelCode`, `ModelEditionOID`, CylinderCapacity, EngineType, PowerKW,GearBoxType,CO2EmissionMin, CO2EmissionMax,EmissionNorm,FuelType) "
                + " VALUES ('{0}', '{1}', '{2}', '{3}', '{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}')";
            query = string.Format(query, subModelPrice.ModelOid, subModelPrice.ModelYearOid, string.IsNullOrEmpty(subModelPrice.Name) ? "" : subModelPrice.Name, subModelPrice.ModelCode, subModelPrice.ModelEditionOid,
                string.IsNullOrEmpty(subModelPrice.CylinderCapacity.ToString()) ? "" : Utils.toStringFormat(subModelPrice.CylinderCapacity),
                string.IsNullOrEmpty(subModelPrice.EngineType) ? "" : subModelPrice.EngineType.ToString(),
                string.IsNullOrEmpty(subModelPrice.PowerKW.ToString()) ? "" : subModelPrice.PowerKW.ToString(),
                string.IsNullOrEmpty(subModelPrice.GearBoxType) ? "" : subModelPrice.GearBoxType.ToString(),
                string.IsNullOrEmpty(subModelPrice.CO2EmissionMin.ToString()) ? "" : Utils.toStringFormat(subModelPrice.CO2EmissionMin),
                string.IsNullOrEmpty(subModelPrice.CO2EmissionMax.ToString()) ? "" : Utils.toStringFormat(subModelPrice.CO2EmissionMax),
                string.IsNullOrEmpty(subModelPrice.CO2EmissionAvg.ToString()) ? "" : Utils.toStringFormat(subModelPrice.CO2EmissionAvg),
                string.IsNullOrEmpty(subModelPrice.EmissionNorm) ? "" : subModelPrice.EmissionNorm.ToString(),
                string.IsNullOrEmpty(subModelPrice.FuelType) ? "" : subModelPrice.FuelType.ToString()
                );
            result = db.saveModel(query);
            return result;
        }

        public static int GetSubmodelOid(SubModelPrice subModelPrice)
        {
            int result = -1;
            var db = new clsDb();
            var query = "select * from submodel where ModelOID = {0} and ModelYearOID = {1} and `name` like '{2}' and ModelCode like '{3}' and  ModelEditionOID = {4}";
            query = string.Format(query, subModelPrice.ModelOid, subModelPrice.ModelYearOid, subModelPrice.Name, subModelPrice.ModelCode, subModelPrice.ModelEditionOid);

            DataTable dt = new DataTable();
            dt = db.GetDataTable(query);
            if (dt != null)
            {
                if (dt.Rows.Count > 0)
                {
                    DataRow r = dt.Rows[0];
                    result = (int)r[FieldNames.OID];
                    return result;

                }
                else
                    return -1;
            }
            else
                return -1;

        }



        public static int GetSubModelPriceBySubModelOid(int subModelOid, string pricelistdate)
        {
            int result = -1;
            string query = "select * from submodelprice s where s.SubmodelOID = {0} and PriceListDate = STR_TO_DATE('{1}', '%Y.%m.%d')";
            query = string.Format(query, subModelOid, pricelistdate);
            var db = new clsDb();

            DataTable dt = new DataTable();
            dt = db.GetDataTable(query);
            if (dt != null)
            {
                if (dt.Rows.Count > 0)
                {
                    DataRow r = dt.Rows[0];
                    result = (int)r[FieldNames.OID];
                    return result;

                }
                else
                    return -1;
            }
            else
                return -1;

        }
        public static int UpdateSubModelPrice(int oid, SubModelPrice s)
        {
            var db = new clsDb();

            var query2 = "UPDATE `submodelprice` "
                + " SET `TaxPercentage1` = '{0}', `TaxPercentage2` = '{1}', `TaxPercentage3` = '{2}',"
                + " `TaxAmount1` = '{3}', `TaxAmount2` = '{4}', `TaxAmount3` = '{5}', `DealerNetPrice` = '{6}', `DealerNetPriceVAT` = '{7}',"
                + " `RetailPrice` = '{8}', `RetailPriceVAT` = '{9}' "
            + "WHERE OID = {10}";
            //query2 = string.Format(query2, s.TaxPercentage1, s.TaxPercentage2, s.TaxPercentage3,
            //    s.TaxAmount1, s.TaxAmount2, s.TaxAmount3, s.DealerNetPrice, s.DealerNetPriceVAT,
            //    s.RetailPrice, s.RetailPriceVAT, oid);
            query2 = string.Format(query2, string.IsNullOrEmpty(s.TaxPercentage1.ToString()) ? "0" : Utils.toStringFormat(s.TaxPercentage1),
                        string.IsNullOrEmpty(s.TaxPercentage2.ToString()) ? "0" : Utils.toStringFormat(s.TaxPercentage2),
                        string.IsNullOrEmpty(s.TaxPercentage3.ToString()) ? "0" : Utils.toStringFormat(s.TaxPercentage3),
                        string.IsNullOrEmpty(s.TaxAmount1.ToString()) ? "0" : Utils.toStringFormat(s.TaxAmount1),
                        string.IsNullOrEmpty(s.TaxAmount2.ToString()) ? "0" : Utils.toStringFormat(s.TaxAmount2),
                        string.IsNullOrEmpty(s.TaxAmount3.ToString()) ? "0" : Utils.toStringFormat(s.TaxAmount3),
                        string.IsNullOrEmpty(s.DealerNetPrice.ToString()) ? "0" : Utils.toStringFormat(s.DealerNetPrice),
                        string.IsNullOrEmpty(s.DealerNetPriceVAT.ToString()) ? "0" : Utils.toStringFormat(s.DealerNetPriceVAT),
                        string.IsNullOrEmpty(s.RetailPrice.ToString()) ? "0" : Utils.toStringFormat(s.RetailPrice),
                        string.IsNullOrEmpty(s.RetailPriceVAT.ToString()) ? "0" : Utils.toStringFormat(s.RetailPriceVAT),
                        oid);
            var result = db.RunQuery(query2);
            return result;
        }


        public static int CreateBulkSubModelPrice(List<SubModelPrice> ls)
        {
            if (ls != null && ls.Count > 0)
            {
                foreach (var subModelPrice in ls)
                {
                    if (string.IsNullOrEmpty(subModelPrice.SubModelOid.ToString()))
                        MessageBox.Show(subModelPrice.SubModelName);

                }


                var sql = "INSERT INTO `submodelprice` (`SubmodelOID`, `PriceListDate`, `CountryOID`, `CurrencyOID`, `TaxPercentage1`, `TaxPercentage2`, `TaxPercentage3`, `TaxAmount1`, `TaxAmount2`, `TaxAmount3`, `DealerNetPrice`, `DealerNetPriceVAT`, `RetailPrice`, `RetailPriceVAT`) VALUES ";


                foreach (var s in ls)
                {
                    var val = " ({0}, STR_TO_DATE('{1}', '%Y-%m-%d'), '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}'),";
                    //sql += string.Format(val,
                    val = string.Format(val, s.SubModelOid.ToString(),
                        s.PricelistDate.ToString("yyyy-MM-dd"),
                        s.CountryOid,
                        s.CurrencyOid,
                        string.IsNullOrEmpty(Utils.toStringFormat(s.TaxPercentage1)) ? "0" : Utils.toStringFormat(s.TaxPercentage1),
                        string.IsNullOrEmpty(Utils.toStringFormat(s.TaxPercentage2)) ? "0" : Utils.toStringFormat(s.TaxPercentage2),
                        string.IsNullOrEmpty(Utils.toStringFormat(s.TaxPercentage3)) ? "0" : Utils.toStringFormat(s.TaxPercentage3),
                        string.IsNullOrEmpty(Utils.toStringFormat(s.TaxAmount1)) ? "0" : Utils.toStringFormat(s.TaxAmount1),
                        string.IsNullOrEmpty(Utils.toStringFormat(s.TaxAmount2)) ? "0" : Utils.toStringFormat(s.TaxAmount2),
                        string.IsNullOrEmpty(Utils.toStringFormat(s.TaxAmount3)) ? "0" : Utils.toStringFormat(s.TaxAmount3),
                        string.IsNullOrEmpty(Utils.toStringFormat(s.DealerNetPrice)) ? "0" : Utils.toStringFormat(s.DealerNetPrice),
                        string.IsNullOrEmpty(Utils.toStringFormat(s.DealerNetPriceVAT)) ? "0" : Utils.toStringFormat(s.DealerNetPriceVAT),
                        string.IsNullOrEmpty(Utils.toStringFormat(s.RetailPrice)) ? "0" : Utils.toStringFormat(s.RetailPrice),
                        string.IsNullOrEmpty(Utils.toStringFormat(s.RetailPriceVAT)) ? "0" : Utils.toStringFormat(s.RetailPriceVAT));
                    //  MessageBox.Show(val);
                    sql += val;

                }
                var db = new clsDb();
                sql = sql.Remove(sql.Length - 1);
                //  MessageBox.Show(sql);
                var result = db.RunQuery(sql);
                return result;


            }
            else
            {
                return 0;
            }
        }

        //public static int UpdateBulkSubModelPrice(List<SubModelPrice> ls)
        //{

        //}


        public static DataTable GetVat()
        {
            string sql = "Select * from vat order by name ";
            var db = new clsDb();
            var dt = new DataTable();
            return db.GetDataTable(sql);
        }

        public static int CreateBulkVat(List<Vat> la)
        {
            int ret = 1;
            string sql = "INSERT INTO vat (`code`,`name`,VATPC) VALUES ";
            foreach (var v in la)
            {
                string val = string.Format("('{0}','{1}','{2}'),", v.Code, v.VatName, Utils.CommaToDot(v.VATPC.ToString()));
                sql += val;

            }
            sql = sql.Remove(sql.LastIndexOf(","));
            var db = new clsDb();
            ret = db.RunQuery(sql);
            return ret;
        }



        public static void UpdateVat(Vat v)
        {

            string sql = "UPDATE vat SET `code` = '{0}' , `name` = '{1}', vatpc = '{2}' WHERE oid = {3}";
            sql = string.Format(sql, v.Code, v.VatName, Utils.CommaToDot(v.VATPC.ToString()), v.OID);
            var db = new clsDb();
            db.RunQuery(sql);
        }

        public static DataTable GetAllCountry()
        {
            string sql = " SELECT oid,code,name, CASE IsDefault WHEN 1 THEN  TRUE	WHEN 0 THEN FALSE END AS IsDefault , VatDefault FROM country";
            clsDb db = new clsDb();
            DataTable dt = new DataTable();
            dt = db.GetDataTable(sql);
            return dt;
        }

        public static int CreateBulkCountry(List<CountryCode> la)
        {
            int ret = -2;
            string sql = "INSERT INTO `Country` (`Code`,`Name`,IsDefault, VatDefault) VALUES ";
            foreach (var c in la)
            {
                string val = string.Format("('{0}','{1}',{2},'{3}'),", c.Code, c.Name, c.IsDefault == true ? 1 : 0, c.VatDefault);
                sql += val;

            }
            sql = sql.Remove(sql.LastIndexOf(","));
            var db = new clsDb();
            ret = db.RunQuery(sql);
            return ret;
        }


        internal static void UpdateCountry(CountryCode c)
        {
            string sql = "UPDATE `country` SET `code` = '{0}' , `name` = '{1}', IsDefault = {2}, VatDefault= '{3}' WHERE oid = {4}";
            sql = string.Format(sql, c.Code, c.Name, c.IsDefault == true ? 1 : 0, c.VatDefault, c.OID);
            var db = new clsDb();
            db.RunQuery(sql);
        }

        public static string GetTaxFromCountry(string countryoid)
        {
            string sql = "SELECT v.VATPC FROM `country` c INNER JOIN vat v on c.VatDefault = v.OID WHERE c.OID = {0}";
            sql = string.Format(sql, countryoid);
            var db = new clsDb();
            DataTable dt = new DataTable();
            dt = db.GetDataTable(sql);
            string ret = "";
            if (dt != null)
                if (dt.Rows.Count > 0)
                {
                    ret = dt.Rows[0].ItemArray[0].ToString();
                }
            if (string.IsNullOrEmpty(ret))
                ret = "1";
            return ret;

        }

    }
}
